<?php

class ComplianceReport {

    public function AmlReport($Filters) {

        ## Error Messages
        $_error = array(
            '<script charset="utf-8" type="text/javascript">alert(\'Erro de Consulta, redefina os parametros.\');</script>',
            '<script charset="utf-8" type="text/javascript">alert(\'Introduza um valor de consulta valido.\');</script>',
            '<script charset="utf-8" type="text/javascript">alert("A consulta devolveu demasiados resultados, redefina a consulta.");</script>',
            ''
        );

## Labels
        $_label1 = array('', 'Remetente', 'Destinatario');

        $_condAmount = array("FraEnviado),SUM(FraARecibir", "FraEnviado", "FraARecibir");
        $_condNumber = array('', 'FraCliNumero', 'FraBnfNumero');
        $_condName = array('', 'FraCliNombre', 'FraBnfNombre');
## Verifica Consulta
        if ($Filters['amqFraModo'] == '' || !isset($Filters['amqOrigen']) || $Filters['SRCID'] == '' || $Filters['SRCVALUE'] == '' || $Filters['TimeStart'] == '' || !isset($Filters['TimeEnd']) || $Filters['amqGroup'] == '') {
            die($_error[0]);
        }
        $Channel = $Filters['amqFraModo'];
        $Origem = $Filters['amqOrigen'];
        $Consulta = $Filters['SRCID'];
        $ConsultaValor = $Filters['SRCVALUE'];
        $ConsultaInicio = $Filters['TimeStart'];
        $ConsultaFim = $Filters['TimeEnd'];

        $FiltroGrupo = $Filters['amqGroup'];
        $FiltroGrupoDetalhe = array(
            0 => array(),
            1 => array('FraCliNombre', 'FraCliDNI', 'FraCliTelefono,FraCliTelefono2', 'FraClPais', 'FraBnfPais', 'FraCliBirthDate'),
            2 => array('FraBnfNombre', 'FraBnfCedula', 'FraBnfTelefono', 'FraClPais', 'FraBnfPais', 'FraBnfBirthDate')
        );
## Desdobra Grupos
        $grpField = '';
        for ($i = 0; $i < count($FiltroGrupo); $i++) {
            $grpField .= $FiltroGrupoDetalhe[$Channel][$FiltroGrupo[$i]] . ',';
        }
        $grpField = substr($grpField, 0, strlen($grpField) - 1);
#Parametros de abrangencia
        $Origen = '';
        $Modo = '';
        if ($Origem > 0) {
            $Origen = ' AND FraOrigen0 = ' . $Origem;
        }
        if ($Channel > 0) {
            $Modo = ' AND FraModo = ' . $Channel;
        }

        $where = '';
        $grpCond = '';
        $hvgCondition = '';

        $grpLabel1 = 1;
        $grpLabel2 = 1;
        $grpLabel3 = 1;
        $having = '';
        $tot = 0;

        $grpCol3 = 'COUNT(DISTINCT FraBnfPais)';

# Elemento a consultar
        if ($Consulta == 27) {
            $Element = 2;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 28) {
            $Element = 2;
            $hvgCondition = $_condAmount[$Channel];
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 29) {
            $Element = 2;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 30) {
            $Element = 2;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 31) {
            $Element = 2;
            $hvgCondition = 'COUNT(DISTINCT FraBnfNumero)';
            $selectCond = "SUM($_condAmount[$Channel])";
        } elseif ($Consulta == 32) {
            $Element = 2;
            $hvgCondition = $_condName[$Channel];
            $selectCond = "SUM($_condAmount[$Channel])";
        } elseif ($Consulta == 20) {
            $Element = 1;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 21) {
            $Element = 1;
            $hvgCondition = $_condAmount[$Channel];
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 22) {
            $Element = 1;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 23) {
            $Element = 1;
            $hvgCondition = "SUM($_condAmount[$Channel])";
            $selectCond = $hvgCondition;
        } elseif ($Consulta == 25) {
            $Element = 1;
            $hvgCondition = 'COUNT(DISTINCT FraCliNumero)';
            $selectCond = "SUM($_condAmount[$Channel])";
        } elseif ($Consulta == 26) {
            $Element = 1;
            $hvgCondition = $_condName[$Channel];
            $selectCond = "SUM($_condAmount[$Channel])";
        }

        if (!is_numeric($ConsultaValor) && $hvgCondition != $_condName[$Channel]) {
            die($_error[1]);
        } else {
            $ConsultaValor = '"' . $ConsultaValor . '"';
        }
        $having = "HAVING COL10 >= $ConsultaValor";

        $GETMIN = $GLOBALS['MYT']->db_array("SELECT FraID AS QR FROM facturas
		WHERE YEAR(FraFechaIngreso) = YEAR('$ConsultaInicio') AND MONTH(FraFechaIngreso) = MONTH('$ConsultaInicio') ORDER BY FraID ASC LIMIT 0,1");
        $GETMAX = $GLOBALS['MYT']->db_array("SELECT FraID AS QR FROM facturas
		WHERE YEAR(FraFechaIngreso) = YEAR('$ConsultaFim') AND MONTH(FraFechaIngreso) = MONTH('$ConsultaFim') ORDER BY FraID DESC LIMIT 0,1");


        $qrAmq = "SELECT
        FraModo,
        $_condNumber[$Channel] AS O_ID,
        MIN(DATE(FraFechaIngreso)) AS COL1,
        $_condName[$Channel] AS COL2,
        CONCAT({$grpField}) AS COL3,
        COUNT(FraID) AS COL4,
        {$selectCond} AS COL5,
        COUNT(DISTINCT FraCliNumero) AS COL6,
        COUNT(DISTINCT FraBnfCedula) AS COL7,
        COUNT(DISTINCT FraCtaBenef) AS COL8,
        {$grpCol3} AS COL9,
        {$hvgCondition} AS COL10,
        FraMonCobro
            FROM facturas
            INNER JOIN facturasextjoins ON FraID = FraExtID
            LEFT JOIN facturas_income ON FraID = FraIncID
            WHERE FraID BETWEEN {$GETMIN[1]['QR']} AND {$GETMAX[1]['QR']} AND FraFechaIngreso BETWEEN '$ConsultaInicio 00:00:00' AND '$ConsultaFim 23:59:59'
            {$where} AND FraStatus NOT IN (1,9,10,21) {$Origen}{$Modo}
            GROUP BY FraModo,$_condAmount[$Channel],COL3
            {$having}";

        $GET = $GLOBALS['MYT']->db_array($qrAmq);
        $CGET = $GET[0];

        return array($_error, $GET, $Element, $_label1, $grpField);
    }

    private function grid_detail($Name, $Value) {
         $sqlNames = '';
         $sqlValues = '';
        "SELECT FraID AS O_ID, CONCAT(FraOrigen0,'-',FraReferenciaAg) AS COL1, FraFechaIngreso AS COL4, FraMonDPago, FraMonCobro, FraBnfNombre AS COL7,FraCliNombre AS COL8, FraEnviado COL2, FraARecibir AS COL3, FraCargo AS COL5, sg_Abreviatura AS COL6
  			FROM facturas
				INNER JOIN facturasextjoins ON FraID = FraExtID
				INNER JOIN hd_statusfacturas ON sg_ID = FraStatus
			WHERE $Name = '$Value' AND FraStatus NOT IN (1,9,10,21)";
        $getNames = preg_split('/,/',$Name);
        $getValues = preg_split('/,/',$Value);
        for($i=0;$i<count($getNames);$i++){
            if(isset($getValues[$i])){
            $sqlNames .= $getNames[$i].',';
            $sqlValues .= $getValues[$i].',';
            }
            
        }
        $sqlNames = substr($sqlNames,0,strlen($sqlNames)-1);
        $sqlValues = substr($sqlValues,0,strlen($sqlValues)-1);
        if ($sqlValues != '') {
            return $GLOBALS['MYT']->db_array("SELECT FraID AS O_ID, CONCAT(FraOrigen0,'-',FraReferenciaAg) AS COL1, FraFechaIngreso AS COL4, FraMonDPago, FraMonCobro, FraBnfNombre AS COL7,FraCliNombre AS COL8, FraEnviado COL2, FraARecibir AS COL3, FraCargo AS COL5, sg_Abreviatura AS COL6
  			FROM facturas
				INNER JOIN facturasextjoins ON FraID = FraExtID
				INNER JOIN hd_statusfacturas ON sg_ID = FraStatus
			WHERE $sqlNames = '$sqlValues' AND FraStatus NOT IN (1,9,10,21)");
        } else {
            return array();
        }
    }

    public function griddetail_display($Element, $SearchParam, $SearchValue) {
        global $SESSST, $CURS;
        $DBGET = $this->grid_detail($SearchParam, $SearchValue);

        $prtStr = '';
        $CARD = $DBGET[0];
        $SUM = 0;
        $SUM2 = 0;

        $prtStr .= '<table style=\"border-bottom:1px solid #CCC;width:100%;padding:4px;cell-spacing:3px">
	<tr>
		<td style="border-bottom:1px solid #CCC;width:3%">N</td>
		<td style="border-bottom:1px solid #CCC;width:3%">Operacao</td>
		<td style="border-bottom:1px solid #CCC;width:6%">Remessa</td>
		<td style="border-bottom:1px solid #CCC;width:8%">Enviado</td>
		<td style="border-bottom:1px solid #CCC;width:8%">Pagar</td>
		<td style="border-bottom:1px solid #CCC;width:25%">Remetente</td>
		<td style="border-bottom:1px solid #CCC;width:25%">Beneficiario</td>
		<td style="border-bottom:1px solid #CCC;width:12%">Data</td>
		<td style="border-bottom:1px solid #CCC;width:3%">Taxa</td>
		<td style="border-bottom:1px solid #CCC;width:3%">Estado</td>
		</tr>';
        for ($i = 1; $i <= $CARD; $i++) {
            $SUM = $SUM + $DBGET[$i]['COL2'];
            $SUM2 = $SUM2 + $DBGET[$i]['COL3'];
            $prtStr .= "<tr style=\"height:20px\">
		<td style=\"border-bottom:1px solid #CCC;\">{$i}</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['O_ID']}</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['COL1']}</td>
		<td style=\"text-align:right;border-bottom:1px solid #CCC;\">" . number_format($DBGET[$i]['COL2'], 2, ',', '.') . " " . $_SESSION[$SESSST . 'CURS'][$DBGET[$i]['FraMonCobro']]['CurLabel'] . "</td>
		<td style=\"text-align:right;border-bottom:1px solid #CCC;\">" . number_format($DBGET[$i]['COL3'], 2, ',', '.') . " " . $_SESSION[$SESSST . 'CURS'][$DBGET[$i]['FraMonDPago']]['CurLabel'] . "</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['COL8']}</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['COL7']}</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['COL4']}</td>
		<td style=\"border-bottom:1px solid #CCC;\">" . number_format($DBGET[$i]['COL5'], 2, ',', '.') . "</td>
		<td style=\"border-bottom:1px solid #CCC;\">{$DBGET[$i]['COL6']}</td>
		</tr>";
        }
        $prtStr .= "<tr style=\"height:24px\"><td colspan=\"3\">Totais:</td><td style=\"text-align:right\"><b>{$SUM} " . $_SESSION[$SESSST . 'CURS'][$DBGET[1]['FraMonCobro']]['CurLabel'] . "</b></td>
			<td style=\"text-align:right\"><b>{$SUM2} " . $_SESSION[$SESSST . 'CURS'][$DBGET[1]['FraMonDPago']]['CurLabel'] . "</b></td></tr></table>";
        echo $prtStr;
    }

    public function grid_display($Result) {

        global $SESSST, $__sysCountries, $__sysFraModo;

        $prtStr = '';
        $tot = 0;
        if ($Result[1][0] > 2000) {
            $prtStr = $Result[0][3];
        } else {
            $prtStr = '';
            $GET = $Result[1];
            $CGET = $Result[1][0];
            $Element = $Result[2];
            $_label1 = $Result[3];
            $grpField = $Result[4];

            $prtStr .= '<div style="background-color:#8fadcc;padding:3px">
<table style="background-color:#FFF;padding:3px;width:100%">
	<tr style="font-weight:200">
		<td NOWRAP style="width:5%">#</td>
		<td NOWRAP style="width:8%">Canal</td>
		<td NOWRAP style="width:8%">ID</td>
		<td NOWRAP style="width:8%">Data</td>
		<td NOWRAP style="width:30%">' . $_label1[$Element] . '</td>
		<td NOWRAP style="width:10%">' . $grpField . '</td>
		<td NOWRAP style="width:5%">Quantidade</td>
		<td NOWRAP style="width:5%">Montante</td>';
            if ($Element != 1) {
                $prtStr .= '<td NOWRAP style="width:5%"># Beneficiarios</td>';
            }
            if ($Element != 2) {
                $prtStr .= '<td NOWRAP style="width:5%"># Remetentes</td>';
            }
            if ($Element != 3) {
                $prtStr .= '<td NOWRAP style="width:5%"># Contas</td>';
            }
            $prtStr .= '<td NOWRAP style="width:5%"># Paises</td>
	</tr>';

            for ($f = 1; $f <= $CGET; $f++) {
                $tot += $GET[$f]['COL5'];
                $prtStr .= "<tr bgcolor=\"" . gridlines($f) . "\">
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\"><img id=\"ico$f\" src=\"_response/bin.php?get=Objects/Tree/plus.gif\"
        onclick=\"gridtoggle('$f','_response/call.php?get=Compliance/a01_result&sysMethod=OR&SearchParam=$grpField&SearchValue={$GET[$f]['COL3']}');\">{$f}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\">{$__sysFraModo[$GET[$f]['FraModo']]}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\">{$GET[$f]['O_ID']}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\">{$GET[$f]['COL1']}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\">{$GET[$f]['COL2']}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;\">";
                if ($grpField == 'FraClPais' || $grpField == 'FraBnfPais') {
                    $prtStr .= $__sysCountries[$GET[$f]['COL3']];
                } else {
                    $prtStr .= $GET[$f]['COL3'];
                }
                $prtStr .= "</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;text-align:right\">{$GET[$f]['COL4']}</td>
	<td NOWRAP style=\"border-bottom:1px solid #BBB;text-align:right\">" . number_format($GET[$f]['COL5'], 2, ',', '') . " " . $_SESSION[$SESSST . 'CURS'][$GET[$f]['FraMonCobro']]['CurLabel'] . "</td>";
                if ($Element != 1) {
                    $prtStr .= "<td style=\"border-bottom:1px solid #BBB;text-align:right\">{$GET[$f]['COL6']}</td>";
                }
                if ($Element != 2) {
                    $prtStr .= "<td style=\"border-bottom:1px solid #BBB;text-align:right\">{$GET[$f]['COL7']}</td>";
                }
                if ($Element != 3) {
                    $prtStr .= "<td style=\"border-bottom:1px solid #BBB;text-align:right\" >{$GET[$f]['COL8']}</td>";
                } $prtStr .= "
	<td NOWRAP style=\"border-bottom:1px solid #BBB;text-align:right\">{$GET[$f]['COL9']}</td></tr>
	<tr><td colspan=\"11\"><div id=\"row{$f}\"></div></td></tr>";
            }
            $prtStr .= "<tr>
	<td colspan=\"6\" style=\"height:20px;border-top:1px solid #BBB;\"></td>
	<td style=\"border-top:1px solid #BBB;text-align:right\"></td>
	<td NOWRAP style=\"border-top:1px solid #BBB;text-align:right\"><b>" . number_format($tot, 2, ',', '') . " " . $_SESSION[$SESSST . 'CURS'][1]['CurLabel'] . "</b></td>";
            if ($Element != 1) {
                $prtStr .= "<td style=\"border-top:1px solid #BBB;text-align:right\"></td>";
            }
            if ($Element != 2) {
                $prtStr .= "<td style=\"border-top:1px solid #BBB;text-align:right\"></td>";
            }
            if ($Element != 3) {
                $prtStr .= "<td style=\"border-top:1px solid #BBB;text-align:right\" ></td>";
            } $prtStr .= "
	<td style=\"border-top:1px solid #BBB;text-align:right\"></td></tr>";
            $prtStr .= '</table></div>';
        }
        return $prtStr;
    }

}

?>
